{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f442e25b",
   "metadata": {},
   "source": [
    "# 导入必要库并且读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "5aeb0c93",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import json\n",
    "import os\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e0c62e73",
   "metadata": {},
   "outputs": [],
   "source": [
    "cities = ['东莞', '中山', '佛山', '广州', '惠州', '江门', '深圳', '清远', '湛江', '珠海']\n",
    "datazs = {}\n",
    "for city in cities:\n",
    "    file_path = f'./在售csv/二手房_{city}.csv'\n",
    "    datazs[city] = pd.read_csv(file_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8eff9e0f",
   "metadata": {},
   "source": [
    "# 查询列表"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2a78ffa",
   "metadata": {},
   "source": [
    "共计29883条在售数据 \n",
    "\n",
    "\n",
    "'编号', '标题', '小区名称', '区域', '户型', '面积', '朝向', '装修情况', '楼层', '关注人数', '发布时间', '总价', '单价'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "269d978a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "东莞在售数据量： (3000, 13)\n",
      "东莞在售列表： ['编号', '标题', '小区名称', '区域', '户型', '面积', '朝向', '装修情况', '楼层', '关注人数', '发布时间', '总价', '单价']\n",
      "中山在售数据量： (2971, 13)\n",
      "中山在售列表： ['编号', '标题', '小区名称', '区域', '户型', '面积', '朝向', '装修情况', '楼层', '关注人数', '发布时间', '总价', '单价']\n",
      "佛山在售数据量： (3000, 13)\n",
      "佛山在售列表： ['编号', '标题', '小区名称', '区域', '户型', '面积', '朝向', '装修情况', '楼层', '关注人数', '发布时间', '总价', '单价']\n",
      "广州在售数据量： (3000, 13)\n",
      "广州在售列表： ['编号', '标题', '小区名称', '区域', '户型', '面积', '朝向', '装修情况', '楼层', '关注人数', '发布时间', '总价', '单价']\n",
      "惠州在售数据量： (3000, 13)\n",
      "惠州在售列表： ['编号', '标题', '小区名称', '区域', '户型', '面积', '朝向', '装修情况', '楼层', '关注人数', '发布时间', '总价', '单价']\n",
      "江门在售数据量： (3000, 13)\n",
      "江门在售列表： ['编号', '标题', '小区名称', '区域', '户型', '面积', '朝向', '装修情况', '楼层', '关注人数', '发布时间', '总价', '单价']\n",
      "深圳在售数据量： (3000, 13)\n",
      "深圳在售列表： ['编号', '标题', '小区名称', '区域', '户型', '面积', '朝向', '装修情况', '楼层', '关注人数', '发布时间', '总价', '单价']\n",
      "清远在售数据量： (2912, 13)\n",
      "清远在售列表： ['编号', '标题', '小区名称', '区域', '户型', '面积', '朝向', '装修情况', '楼层', '关注人数', '发布时间', '总价', '单价']\n",
      "湛江在售数据量： (3000, 13)\n",
      "湛江在售列表： ['编号', '标题', '小区名称', '区域', '户型', '面积', '朝向', '装修情况', '楼层', '关注人数', '发布时间', '总价', '单价']\n",
      "珠海在售数据量： (3000, 13)\n",
      "珠海在售列表： ['编号', '标题', '小区名称', '区域', '户型', '面积', '朝向', '装修情况', '楼层', '关注人数', '发布时间', '总价', '单价']\n"
     ]
    }
   ],
   "source": [
    "for i in cities:\n",
    "    print(f'{i}在售数据量：',datazs[i].shape)\n",
    "    print(f'{i}在售列表：',datazs[i].columns.tolist())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8484d0e8",
   "metadata": {},
   "source": [
    "# 缺失值处理"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f2b7940",
   "metadata": {},
   "source": [
    "缺失值处理：中山、惠州、江门、清远存在部分缺失值\n",
    "\n",
    "发现因为户型车位与其他类型的属性不符合，删除户型为车位的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "59a2ff2d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "东莞在售: 编号      0\n",
      "标题      0\n",
      "小区名称    0\n",
      "区域      0\n",
      "户型      0\n",
      "面积      0\n",
      "朝向      0\n",
      "装修情况    0\n",
      "楼层      0\n",
      "关注人数    0\n",
      "发布时间    0\n",
      "总价      0\n",
      "单价      0\n",
      "dtype: int64\n",
      "中山在售: 编号      0\n",
      "标题      0\n",
      "小区名称    0\n",
      "区域      0\n",
      "户型      0\n",
      "面积      0\n",
      "朝向      0\n",
      "装修情况    0\n",
      "楼层      1\n",
      "关注人数    0\n",
      "发布时间    0\n",
      "总价      0\n",
      "单价      0\n",
      "dtype: int64\n",
      "佛山在售: 编号      0\n",
      "标题      0\n",
      "小区名称    0\n",
      "区域      0\n",
      "户型      0\n",
      "面积      0\n",
      "朝向      0\n",
      "装修情况    0\n",
      "楼层      0\n",
      "关注人数    0\n",
      "发布时间    0\n",
      "总价      0\n",
      "单价      0\n",
      "dtype: int64\n",
      "广州在售: 编号      0\n",
      "标题      0\n",
      "小区名称    0\n",
      "区域      0\n",
      "户型      0\n",
      "面积      0\n",
      "朝向      0\n",
      "装修情况    0\n",
      "楼层      0\n",
      "关注人数    0\n",
      "发布时间    0\n",
      "总价      0\n",
      "单价      0\n",
      "dtype: int64\n",
      "惠州在售: 编号      0\n",
      "标题      0\n",
      "小区名称    0\n",
      "区域      0\n",
      "户型      0\n",
      "面积      0\n",
      "朝向      0\n",
      "装修情况    0\n",
      "楼层      1\n",
      "关注人数    0\n",
      "发布时间    0\n",
      "总价      0\n",
      "单价      0\n",
      "dtype: int64\n",
      "江门在售: 编号      0\n",
      "标题      0\n",
      "小区名称    0\n",
      "区域      0\n",
      "户型      1\n",
      "面积      1\n",
      "朝向      1\n",
      "装修情况    1\n",
      "楼层      1\n",
      "关注人数    0\n",
      "发布时间    0\n",
      "总价      0\n",
      "单价      0\n",
      "dtype: int64\n",
      "深圳在售: 编号      0\n",
      "标题      0\n",
      "小区名称    0\n",
      "区域      0\n",
      "户型      0\n",
      "面积      0\n",
      "朝向      0\n",
      "装修情况    0\n",
      "楼层      0\n",
      "关注人数    0\n",
      "发布时间    0\n",
      "总价      0\n",
      "单价      0\n",
      "dtype: int64\n",
      "清远在售: 编号      0\n",
      "标题      0\n",
      "小区名称    0\n",
      "区域      0\n",
      "户型      0\n",
      "面积      0\n",
      "朝向      0\n",
      "装修情况    0\n",
      "楼层      1\n",
      "关注人数    0\n",
      "发布时间    0\n",
      "总价      0\n",
      "单价      0\n",
      "dtype: int64\n",
      "湛江在售: 编号      0\n",
      "标题      0\n",
      "小区名称    0\n",
      "区域      0\n",
      "户型      0\n",
      "面积      0\n",
      "朝向      0\n",
      "装修情况    0\n",
      "楼层      0\n",
      "关注人数    0\n",
      "发布时间    0\n",
      "总价      0\n",
      "单价      0\n",
      "dtype: int64\n",
      "珠海在售: 编号      0\n",
      "标题      0\n",
      "小区名称    0\n",
      "区域      0\n",
      "户型      0\n",
      "面积      0\n",
      "朝向      0\n",
      "装修情况    0\n",
      "楼层      0\n",
      "关注人数    0\n",
      "发布时间    0\n",
      "总价      0\n",
      "单价      0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "for i in cities:\n",
    "    print(f'{i}在售:',datazs[i].isnull().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "55902676",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "中山在售数据中存在缺失值的行：共1行\n",
      "\t编号\t标题\t小区名称\t区域\t户型\t面积\t朝向\t装修情况\t楼层\t关注人数\t发布时间\t总价\t单价\r\n",
      "1948\t105115000000.0\t石岐大信商圈 清溪花园 低楼层两房 格局方正 保养好\t清溪花园\t实验小学\t车位\t60平米\t南\t平房\tnan\t1\t一年前发布\t30.0\t5,000元/平\r\n",
      "\n",
      "\n",
      "惠州在售数据中存在缺失值的行：共1行\n",
      "\t编号\t标题\t小区名称\t区域\t户型\t面积\t朝向\t装修情况\t楼层\t关注人数\t发布时间\t总价\t单价\r\n",
      "2981\t105118000000.0\t中洲云睿府 车位\t中洲云睿府\t河南岸\t车位\t42平米\t东\t塔楼\tnan\t0\t9个月以前发布\t55.0\t13,096元/平\r\n",
      "\n",
      "\n",
      "江门在售数据中存在缺失值的行：共1行\n",
      "\t编号\t标题\t小区名称\t区域\t户型\t面积\t朝向\t装修情况\t楼层\t关注人数\t发布时间\t总价\t单价\r\n",
      "880\t105108000000.0\t地下车库，停车方便，随时可看！！！！！！\t海逸城邦\t北新区\tnan\tnan\tnan\tnan\tnan\t0\t一年前发布\t18.8万\t6,715元/平\r\n",
      "\n",
      "\n",
      "清远在售数据中存在缺失值的行：共1行\n",
      "\t编号\t标题\t小区名称\t区域\t户型\t面积\t朝向\t装修情况\t楼层\t关注人数\t发布时间\t总价\t单价\r\n",
      "2152\t105111147694\t幸福世家 车位\t幸福世家\t新城\t车位\t361.23平米\t南 北\t暂无数据\tnan\t0\t一年前发布\t357.7\t9,903元/平\r\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for city in cities:\n",
    "    df = datazs[city]\n",
    "    missing_rows = df[df.isnull().any(axis=1)]  # 筛选出存在缺失值的行\n",
    "    if not missing_rows.empty:\n",
    "        print(f\"\\n{city}在售数据中存在缺失值的行：共{len(missing_rows)}行\")\n",
    "        print(missing_rows.to_csv(sep='\\t', na_rep='nan')) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d19d9914",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "东莞在售数据量： (3000, 13)\n",
      "中山在售数据量： (2970, 13)\n",
      "佛山在售数据量： (3000, 13)\n",
      "广州在售数据量： (3000, 13)\n",
      "惠州在售数据量： (2999, 13)\n",
      "江门在售数据量： (2999, 13)\n",
      "深圳在售数据量： (3000, 13)\n",
      "清远在售数据量： (2911, 13)\n",
      "湛江在售数据量： (3000, 13)\n",
      "珠海在售数据量： (3000, 13)\n"
     ]
    }
   ],
   "source": [
    "# 循环删除各城市的车位数据\n",
    "for city in cities:\n",
    "    datazs[city] = datazs[city][datazs[city]['户型'] != '车位']\n",
    "        \n",
    "datazs['江门'] = datazs['江门'].drop(880)\n",
    "    \n",
    "for city in cities:\n",
    "    datazs[city] = datazs[city].reset_index(drop=True)\n",
    "\n",
    "for i in cities:\n",
    "    print(f'{i}在售数据量：',datazs[i].shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbb0abec",
   "metadata": {},
   "source": [
    "# 重复值处理"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cfd281a",
   "metadata": {},
   "source": [
    "保存结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4410ce85",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "东莞市存在 777 条重复记录，重复比例: 25.90%\n",
      "中山市存在 582 条重复记录，重复比例: 19.60%\n",
      "佛山市存在 880 条重复记录，重复比例: 29.33%\n",
      "广州市存在 862 条重复记录，重复比例: 28.73%\n",
      "惠州市存在 855 条重复记录，重复比例: 28.51%\n",
      "江门市存在 474 条重复记录，重复比例: 15.81%\n",
      "深圳市存在 926 条重复记录，重复比例: 30.87%\n",
      "清远市存在 315 条重复记录，重复比例: 10.82%\n",
      "湛江市存在 477 条重复记录，重复比例: 15.90%\n",
      "珠海市存在 769 条重复记录，重复比例: 25.63%\n",
      "\n",
      "已将重复数据统计结果保存至: 重复统计结果\\在售重复数据统计.json\n",
      "东莞市已删除 777 条重复记录\n",
      "中山市已删除 582 条重复记录\n",
      "佛山市已删除 880 条重复记录\n",
      "广州市已删除 862 条重复记录\n",
      "惠州市已删除 855 条重复记录\n",
      "江门市已删除 474 条重复记录\n",
      "深圳市已删除 926 条重复记录\n",
      "清远市已删除 315 条重复记录\n",
      "湛江市已删除 477 条重复记录\n",
      "珠海市已删除 769 条重复记录\n",
      "\n",
      "所有城市的重复数据已删除完毕\n"
     ]
    }
   ],
   "source": [
    "# 创建保存统计结果的字典\n",
    "duplicate_stats = {}\n",
    "\n",
    "for city in cities:\n",
    "    df = datazs[city]\n",
    "    \n",
    "    # 计算重复行数\n",
    "    duplicates = df[df.duplicated()]\n",
    "    duplicate_count = len(duplicates)\n",
    "    duplicate_ratio = duplicate_count / len(df) if len(df) > 0 else 0\n",
    "    \n",
    "    # 记录统计结果\n",
    "    duplicate_stats[city] = {\n",
    "        \"重复比例\": f\"{duplicate_ratio:.2%}\",\n",
    "        \"重复记录数量\": duplicate_count,\n",
    "    }\n",
    "    \n",
    "    print(f\"{city}市存在 {duplicate_count} 条重复记录，重复比例: {duplicate_ratio:.2%}\")\n",
    "\n",
    "output_dir = \"重复统计结果\"\n",
    "os.makedirs(output_dir, exist_ok=True)\n",
    "json_path = os.path.join(output_dir, \"在售重复数据统计.json\")\n",
    "\n",
    "with open(json_path, 'w', encoding='utf-8') as f:\n",
    "    json.dump(duplicate_stats, f, ensure_ascii=False, indent=2)\n",
    "\n",
    "print(f\"\\n已将重复数据统计结果保存至: {json_path}\")\n",
    "\n",
    "for city in cities:\n",
    "    original_len = len(datazs[city])\n",
    "    datazs[city] = datazs[city].drop_duplicates()\n",
    "    deleted_count = original_len - len(datazs[city])\n",
    "    \n",
    "    if deleted_count > 0:\n",
    "        print(f\"{city}市已删除 {deleted_count} 条重复记录\")\n",
    "    \n",
    "    # 重置索引\n",
    "    datazs[city] = datazs[city].reset_index(drop=True)\n",
    "\n",
    "print(\"\\n所有城市的重复数据已删除完毕\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c05ca42",
   "metadata": {},
   "source": [
    "# 重构关系结构"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "27e492a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "city_pinyin_map = {\n",
    "    \"东莞\": \"dg\",\n",
    "    \"中山\": \"zs\",\n",
    "    \"佛山\": \"fs\",\n",
    "    \"广州\": \"gz\",\n",
    "    \"惠州\": \"hz\",\n",
    "    \"江门\": \"jm\",\n",
    "    \"深圳\": \"sz\",\n",
    "    \"清远\": \"qy\",\n",
    "    \"湛江\": \"zj\",\n",
    "    \"珠海\": \"zh\"\n",
    "}\n",
    "for city in cities:\n",
    "    df = datazs[city]\n",
    "    df['城市'] = city\n",
    "    city_pinyin = city_pinyin_map[city]\n",
    "    df['新编号'] = [f\"{city_pinyin}{i}\" for i in df.index] # 添加新编号列\n",
    "    df = df.drop('编号',axis=1)\n",
    "    datazs[city] = df # 更新原始数据"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87675574",
   "metadata": {},
   "source": [
    "# TOP50、TOP100数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ba3d7b45",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "东莞 TOP50房源统计完成，数据量：50\n",
      "中山 TOP50房源统计完成，数据量：50\n",
      "佛山 TOP50房源统计完成，数据量：50\n",
      "广州 TOP50房源统计完成，数据量：50\n",
      "惠州 TOP50房源统计完成，数据量：50\n",
      "江门 TOP50房源统计完成，数据量：50\n",
      "深圳 TOP50房源统计完成，数据量：50\n",
      "清远 TOP50房源统计完成，数据量：50\n",
      "湛江 TOP50房源统计完成，数据量：50\n",
      "珠海 TOP50房源统计完成，数据量：50\n",
      "东莞 TOP50数据已保存\n",
      "中山 TOP50数据已保存\n",
      "佛山 TOP50数据已保存\n",
      "广州 TOP50数据已保存\n",
      "惠州 TOP50数据已保存\n",
      "江门 TOP50数据已保存\n",
      "深圳 TOP50数据已保存\n",
      "清远 TOP50数据已保存\n",
      "湛江 TOP50数据已保存\n",
      "珠海 TOP50数据已保存\n",
      "全省TOP100数据已保存\n"
     ]
    }
   ],
   "source": [
    "# 1. 各城市TOP50房源统计\n",
    "city_top50 = {}\n",
    "for city, df in datazs.items():\n",
    "    top_df = df.sort_values(by='关注人数', ascending=False).head(50)\n",
    "    city_top50[city] = top_df\n",
    "    print(f\"{city} TOP50房源统计完成，数据量：{len(top_df)}\")\n",
    "\n",
    "# 2. 合并全省数据并统计TOP100\n",
    "province_data = pd.concat(datazs.values(), ignore_index=True)\n",
    "province_top100 = province_data.sort_values(by='关注人数', ascending=False).head(100)\n",
    "\n",
    "# 3. 创建保存目录（新增代码）\n",
    "for dir_name in ['top50', 'top100']:\n",
    "    if not os.path.exists(dir_name):\n",
    "        os.makedirs(dir_name)\n",
    "        print(f\"目录 '{dir_name}' 创建成功\")\n",
    "\n",
    "# 4. 结果保存（路径调整为相对路径）\n",
    "# 保存各城市TOP50到本地\n",
    "for city, df in city_top50.items():\n",
    "    df.to_csv(f'top50/二手房_{city}_TOP50.csv', index=False)\n",
    "    print(f\"{city} TOP50数据已保存\")\n",
    "\n",
    "# 保存全省TOP100到本地\n",
    "province_top100.to_csv('top100/二手房_全省TOP100.csv', index=False)\n",
    "print(\"全省TOP100数据已保存\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78d5a069",
   "metadata": {},
   "source": [
    "# 数据清洗和数据划分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d8fd7b44",
   "metadata": {},
   "outputs": [],
   "source": [
    "TOP_N = 50  # 城市top房源数量\n",
    "PROVINCE_TOP_N = 100  # 全省top房源数量\n",
    "# 关注人数分类阈值\n",
    "ATTENTION_LEVELS = {\n",
    "    '低关注': (0, 10),\n",
    "    '中关注': (10, 50),\n",
    "    '高关注': (50, np.inf)\n",
    "}\n",
    "\n",
    "# 发布时间分类阈值（天数）\n",
    "TIME_LEVELS = {\n",
    "    '近一个月发布': (0, 30),\n",
    "    '近三个月发布': (30, 90),\n",
    "    '近六个月发布': (90, 180),\n",
    "    '半年以上发布': (180, np.inf)\n",
    "}\n",
    "\n",
    "# 总价分类阈值（万元）\n",
    "PRICE_LEVELS = {\n",
    "    '50万以内': (0, 50),\n",
    "    '50-100万': (50, 100),\n",
    "    '100-200万': (100, 200),\n",
    "    '200万以上': (200, np.inf)\n",
    "}\n",
    "\n",
    "# 单价分类阈值（万元/平米）\n",
    "UNIT_PRICE_LEVELS = {\n",
    "    '1万以内': (0, 10000),\n",
    "    '1-3万': (10000, 30000),\n",
    "    '3-5万': (30000, 50000),\n",
    "    '5万以上': (50000, np.inf)\n",
    "}\n",
    "\n",
    "# 面积（平米）\n",
    "UNIT_SIZE = {\n",
    "    '小户型':{0,90},\n",
    "    '中户型':(90,144),\n",
    "    '大户型':(144,200),\n",
    "    '超大户型':(200,np.inf)\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ba1a26ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_data(df):\n",
    "    # 一、处理面积列，提取数字部分\n",
    "    df['面积'] = df['面积'].astype(str).apply(lambda x: re.search(r'(\\d+\\.?\\d*)', x).group(1) if re.search(r'(\\d+\\.?\\d*)', x) else np.nan)\n",
    "    df['面积'] = pd.to_numeric(df['面积'], errors='coerce')\n",
    "    \n",
    "    # 二、处理总价列，提取数字部分\n",
    "    df['总价'] = df['总价'].astype(str).apply(lambda x: re.search(r'(\\d+\\.?\\d*)', x).group(1) if re.search(r'(\\d+\\.?\\d*)', x) else np.nan)\n",
    "    df['总价'] = pd.to_numeric(df['总价'], errors='coerce')\n",
    "    \n",
    "    # 三、处理单价列，提取数字部分\n",
    "    df['单价'] = df['单价'].astype(str).apply(\n",
    "        lambda x: re.search(r'(\\d{1,3}(?:,\\d{3})*)', x).group(1).replace(',', '')if re.search(r'(\\d{1,3}(?:,\\d{3})*)', x) else np.nan\n",
    ")\n",
    "    df['单价'] = pd.to_numeric(df['单价'], errors='coerce')\n",
    "    \n",
    "    # 四、处理发布时间列，转换为天数\n",
    "    def convert_to_days(time_str):\n",
    "        time_str = str(time_str)\n",
    "        if '天' in time_str:\n",
    "            match = re.search(r'(\\d+)', time_str)\n",
    "            return int(match.group(1)) if match else np.nan\n",
    "        elif '月' in time_str:\n",
    "            match = re.search(r'(\\d+)', time_str)\n",
    "            return int(match.group(1)) * 30 if match else np.nan\n",
    "        elif '年' in time_str:\n",
    "            # 中文数字转阿拉伯数字\n",
    "            chinese_num = {'一': 1, '二': 2, '三': 3, '四': 4, '五': 5, '六': 6, '七': 7, '八': 8, '九': 9, '十': 10}\n",
    "            for cn, num in chinese_num.items():\n",
    "                if cn in time_str:\n",
    "                    return num * 365\n",
    "            return np.nan\n",
    "        else:\n",
    "            return np.nan\n",
    "    \n",
    "    df['发布时间'] = df['发布时间'].apply(convert_to_days)\n",
    "    \n",
    "    # 五、处理户型列，拆分为室和厅\n",
    "    def parse_room_hall(house_type):\n",
    "        house_type = str(house_type)\n",
    "        match = re.search(r'(\\d+)室(\\d+)厅', house_type)\n",
    "        if match:\n",
    "            return pd.Series([int(match.group(1)), int(match.group(2))])\n",
    "        else:\n",
    "            return pd.Series([np.nan, np.nan])\n",
    "    \n",
    "    df[['室', '厅']] = df['户型'].apply(parse_room_hall)\n",
    "    \n",
    "    \n",
    "    #六、楼层等级\n",
    "    def classify_floor(floor_str):\n",
    "        # 先检查是否包含低/中/高关键词\n",
    "        for level in ['低', '中', '高']:\n",
    "            if level in floor_str:\n",
    "                return f\"{level}楼层\"\n",
    "        # 处理纯数字楼层（如\"30层\"）\n",
    "        match = re.match(r'^(\\d+)层$', floor_str)\n",
    "        FLOOR_RULES = {\n",
    "            '低楼层': (1, 10),   # 1-10层\n",
    "            '中楼层': (11, 20),  # 11-20层\n",
    "            '高楼层': (21, np.inf)  # 21层以上\n",
    "        }\n",
    "        if match:\n",
    "            floor_num = int(match.group(1))\n",
    "            for category, (low, high) in FLOOR_RULES.items():\n",
    "                if low <= floor_num <= high:  # 包含边界值\n",
    "                    return category\n",
    "        if '地下室' in floor_str:\n",
    "            return '地下室'\n",
    "        return '未分类'\n",
    "    \n",
    "    df['楼层等级'] = df['楼层'].apply(classify_floor)\n",
    "    \n",
    "    # 七、关注等级\n",
    "    def class_guanzhu(guanzhu):\n",
    "        if pd.notna(guanzhu):\n",
    "            for level, (min_num, max_num) in ATTENTION_LEVELS.items():\n",
    "                if min_num <= guanzhu <= max_num:\n",
    "                    return level\n",
    "        return '未分类'\n",
    "    \n",
    "    df['关注等级'] = df['关注人数'].apply(class_guanzhu)\n",
    "    \n",
    "     # 八、发布区间\n",
    "    def class_time(t):\n",
    "        if pd.notna(t):\n",
    "            for level, (min_num, max_num) in TIME_LEVELS.items():\n",
    "                if min_num <= t <= max_num:\n",
    "                    return level\n",
    "        return '未分类'\n",
    "    \n",
    "    df['发布区间'] = df['发布时间'].apply(class_time)\n",
    "    \n",
    "    #九、总价区间\n",
    "    def class_price(p):\n",
    "        if pd.notna(p):\n",
    "            for level, (min_num, max_num) in PRICE_LEVELS.items():\n",
    "                if min_num <= p <= max_num:\n",
    "                    return level\n",
    "        return '未分类'\n",
    "    df['总价区间'] = df['总价'].apply(class_price)\n",
    "    \n",
    "    #九、单价区间\n",
    "    def class_uprice(p):\n",
    "        if pd.notna(p):\n",
    "            for level, (min_num, max_num) in UNIT_PRICE_LEVELS.items():\n",
    "                if min_num <= p <= max_num:\n",
    "                    return level\n",
    "        return '未分类'\n",
    "    df['单价区间'] = df['单价'].apply(class_uprice)\n",
    "    \n",
    "    #十、朝向划分\n",
    "    def map_orientation_type(orientation):\n",
    "        directions = orientation.split()  # 拆分方向列表\n",
    "        dir_set = set(directions)  # 去重\n",
    "\n",
    "        # 单一朝向（8个基础方向）\n",
    "        base_directions = {'东', '南', '西', '北', '东北', '东南', '西北', '西南'}\n",
    "        if len(dir_set) == 1 and dir_set.issubset(base_directions):\n",
    "            return '单一朝向'\n",
    "\n",
    "        # 南北通透（必须同时包含南和北）\n",
    "        if '南' in dir_set and '北' in dir_set:\n",
    "            return '南北通透'\n",
    "\n",
    "        # 东西通透（必须同时包含东和西）\n",
    "        if '东' in dir_set and '西' in dir_set:\n",
    "            return '东西通透'\n",
    "\n",
    "        # 双向组合（2个方向，非南北/东西通透）\n",
    "        if len(dir_set) == 2:\n",
    "            return '双向组合'\n",
    "\n",
    "        # 复杂组合（3个及以上方向）\n",
    "        if len(dir_set) >= 3:\n",
    "            return '复杂组合'\n",
    "\n",
    "        # 异常值（如空值或无效方向）\n",
    "        return '未知'\n",
    "    \n",
    "    df['朝向分类'] = df['朝向'].apply(map_orientation_type)\n",
    "    \n",
    "    \n",
    "    #十一、面积户型\n",
    "    def class_Unitsize(u):\n",
    "        if pd.notna(u):\n",
    "            for level, (min_num, max_num) in UNIT_SIZE.items():\n",
    "                if min_num <= u <= max_num:\n",
    "                    return level\n",
    "        return '未分类'\n",
    "    df['面积户型'] = df['面积'].apply(class_Unitsize)\n",
    "    return df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "1511c8c2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "已处理 东莞 的数据\n",
      "已处理 中山 的数据\n",
      "已处理 佛山 的数据\n",
      "已处理 广州 的数据\n",
      "已处理 惠州 的数据\n",
      "已处理 江门 的数据\n",
      "已处理 深圳 的数据\n",
      "已处理 清远 的数据\n",
      "已处理 湛江 的数据\n",
      "已处理 珠海 的数据\n"
     ]
    }
   ],
   "source": [
    "for city, df in datazs.items():\n",
    "    if not df.empty:\n",
    "        datazs[city] = process_data(df)\n",
    "        print(f\"已处理 {city} 的数据\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb32a39b",
   "metadata": {},
   "source": [
    "'标题','小区名称','区域','户型','面积','朝向','装修情况','楼层','关注人数','发布时间','总价','单价','城市','新编号', '室','厅', '楼层等级'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "09f7984f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "===========================东莞:室================================\n",
      "室\n",
      "3    2113\n",
      "2      96\n",
      "4      11\n",
      "5       3\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:厅================================\n",
      "厅\n",
      "2    1721\n",
      "1     499\n",
      "3       3\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    1964\n",
      "南北通透     196\n",
      "双向组合      59\n",
      "东西通透       3\n",
      "复杂组合       1\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:装修情况================================\n",
      "装修情况\n",
      "精装    987\n",
      "其他    605\n",
      "简装    375\n",
      "毛坯    256\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:楼层等级================================\n",
      "楼层等级\n",
      "高楼层    951\n",
      "中楼层    806\n",
      "低楼层    461\n",
      "地下室      5\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:关注等级================================\n",
      "关注等级\n",
      "低关注    2157\n",
      "中关注      54\n",
      "高关注      12\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:发布区间================================\n",
      "发布区间\n",
      "半年以上发布    1542\n",
      "近六个月发布     317\n",
      "近一个月发布     207\n",
      "近三个月发布     157\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:总价区间================================\n",
      "总价区间\n",
      "100-200万    1540\n",
      "50-100万      445\n",
      "200万以上       171\n",
      "50万以内         67\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:单价区间================================\n",
      "单价区间\n",
      "1-3万    1821\n",
      "1万以内     365\n",
      "3-5万      36\n",
      "5万以上       1\n",
      "Name: count, dtype: int64\n",
      "===========================东莞:面积户型================================\n",
      "面积户型\n",
      "小户型    1664\n",
      "中户型     543\n",
      "大户型      16\n",
      "Name: count, dtype: int64\n",
      "===========================中山:室================================\n",
      "室\n",
      "3    1389\n",
      "2     421\n",
      "4     406\n",
      "1      98\n",
      "5      55\n",
      "6       9\n",
      "7       7\n",
      "8       1\n",
      "9       1\n",
      "0       1\n",
      "Name: count, dtype: int64\n",
      "===========================中山:厅================================\n",
      "厅\n",
      "2    1958\n",
      "1     386\n",
      "0      20\n",
      "3      19\n",
      "4       4\n",
      "5       1\n",
      "Name: count, dtype: int64\n",
      "===========================中山:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    1865\n",
      "南北通透     439\n",
      "双向组合      73\n",
      "东西通透       9\n",
      "复杂组合       2\n",
      "Name: count, dtype: int64\n",
      "===========================中山:装修情况================================\n",
      "装修情况\n",
      "精装    879\n",
      "其他    735\n",
      "毛坯    512\n",
      "简装    262\n",
      "Name: count, dtype: int64\n",
      "===========================中山:楼层等级================================\n",
      "楼层等级\n",
      "中楼层    914\n",
      "高楼层    799\n",
      "低楼层    675\n",
      "Name: count, dtype: int64\n",
      "===========================中山:关注等级================================\n",
      "关注等级\n",
      "低关注    2363\n",
      "中关注      25\n",
      "Name: count, dtype: int64\n",
      "===========================中山:发布区间================================\n",
      "发布区间\n",
      "半年以上发布    1302\n",
      "近一个月发布     482\n",
      "近六个月发布     350\n",
      "近三个月发布     254\n",
      "Name: count, dtype: int64\n",
      "===========================中山:总价区间================================\n",
      "总价区间\n",
      "50-100万     1212\n",
      "100-200万     748\n",
      "50万以内        283\n",
      "200万以上       145\n",
      "Name: count, dtype: int64\n",
      "===========================中山:单价区间================================\n",
      "单价区间\n",
      "1万以内    1592\n",
      "1-3万     790\n",
      "3-5万       3\n",
      "5万以上       3\n",
      "Name: count, dtype: int64\n",
      "===========================中山:面积户型================================\n",
      "面积户型\n",
      "中户型     1423\n",
      "小户型      756\n",
      "大户型      135\n",
      "超大户型      74\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:室================================\n",
      "室\n",
      "3    1279\n",
      "4     449\n",
      "2     259\n",
      "1     109\n",
      "5      23\n",
      "6       1\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:厅================================\n",
      "厅\n",
      "2    1549\n",
      "1     548\n",
      "0      20\n",
      "3       3\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    1709\n",
      "南北通透     302\n",
      "双向组合      95\n",
      "东西通透       9\n",
      "复杂组合       5\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:装修情况================================\n",
      "装修情况\n",
      "精装    1478\n",
      "简装     396\n",
      "毛坯     221\n",
      "其他      25\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:楼层等级================================\n",
      "楼层等级\n",
      "中楼层    826\n",
      "高楼层    782\n",
      "低楼层    512\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:关注等级================================\n",
      "关注等级\n",
      "低关注    2040\n",
      "中关注      79\n",
      "高关注       1\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:发布区间================================\n",
      "发布区间\n",
      "半年以上发布    945\n",
      "近一个月发布    518\n",
      "近三个月发布    359\n",
      "近六个月发布    298\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:总价区间================================\n",
      "总价区间\n",
      "100-200万    895\n",
      "50-100万     754\n",
      "50万以内       268\n",
      "200万以上      203\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:单价区间================================\n",
      "单价区间\n",
      "1-3万    1139\n",
      "1万以内     960\n",
      "3-5万      18\n",
      "5万以上       3\n",
      "Name: count, dtype: int64\n",
      "===========================佛山:面积户型================================\n",
      "面积户型\n",
      "中户型     1253\n",
      "小户型      734\n",
      "大户型      116\n",
      "超大户型      17\n",
      "Name: count, dtype: int64\n",
      "===========================广州:室================================\n",
      "室\n",
      "3    1045\n",
      "2     627\n",
      "4     284\n",
      "1     126\n",
      "5      46\n",
      "6       8\n",
      "7       1\n",
      "8       1\n",
      "Name: count, dtype: int64\n",
      "===========================广州:厅================================\n",
      "厅\n",
      "2    1141\n",
      "1     962\n",
      "0      22\n",
      "3      12\n",
      "4       1\n",
      "Name: count, dtype: int64\n",
      "===========================广州:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    1718\n",
      "南北通透     245\n",
      "双向组合     146\n",
      "复杂组合      17\n",
      "东西通透      12\n",
      "Name: count, dtype: int64\n",
      "===========================广州:装修情况================================\n",
      "装修情况\n",
      "精装    1125\n",
      "简装     600\n",
      "其他     332\n",
      "毛坯      81\n",
      "Name: count, dtype: int64\n",
      "===========================广州:楼层等级================================\n",
      "楼层等级\n",
      "中楼层    835\n",
      "高楼层    726\n",
      "低楼层    577\n",
      "Name: count, dtype: int64\n",
      "===========================广州:关注等级================================\n",
      "关注等级\n",
      "低关注    1976\n",
      "中关注     145\n",
      "高关注      17\n",
      "Name: count, dtype: int64\n",
      "===========================广州:发布区间================================\n",
      "发布区间\n",
      "半年以上发布    763\n",
      "近一个月发布    606\n",
      "近三个月发布    402\n",
      "近六个月发布    367\n",
      "Name: count, dtype: int64\n",
      "===========================广州:总价区间================================\n",
      "总价区间\n",
      "200万以上      892\n",
      "100-200万    857\n",
      "50-100万     343\n",
      "50万以内        46\n",
      "Name: count, dtype: int64\n",
      "===========================广州:单价区间================================\n",
      "单价区间\n",
      "1-3万    1260\n",
      "3-5万     514\n",
      "1万以内     246\n",
      "5万以上     118\n",
      "Name: count, dtype: int64\n",
      "===========================广州:面积户型================================\n",
      "面积户型\n",
      "小户型     1057\n",
      "中户型      956\n",
      "大户型       88\n",
      "超大户型      37\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:室================================\n",
      "室\n",
      "3    1046\n",
      "4     622\n",
      "2     284\n",
      "1     110\n",
      "5      74\n",
      "6       5\n",
      "7       2\n",
      "8       1\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:厅================================\n",
      "厅\n",
      "2    1709\n",
      "1     418\n",
      "0      15\n",
      "3       2\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    1610\n",
      "南北通透     436\n",
      "双向组合      90\n",
      "复杂组合       7\n",
      "东西通透       1\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:装修情况================================\n",
      "装修情况\n",
      "精装    802\n",
      "毛坯    697\n",
      "其他    423\n",
      "简装    222\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:楼层等级================================\n",
      "楼层等级\n",
      "高楼层    1335\n",
      "中楼层     495\n",
      "低楼层     314\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:关注等级================================\n",
      "关注等级\n",
      "低关注    2113\n",
      "中关注      28\n",
      "高关注       3\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:发布区间================================\n",
      "发布区间\n",
      "半年以上发布    903\n",
      "近一个月发布    590\n",
      "近六个月发布    327\n",
      "近三个月发布    324\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:总价区间================================\n",
      "总价区间\n",
      "50-100万     1158\n",
      "50万以内        511\n",
      "100-200万     437\n",
      "200万以上        38\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:单价区间================================\n",
      "单价区间\n",
      "1万以内    1789\n",
      "1-3万     353\n",
      "3-5万       2\n",
      "Name: count, dtype: int64\n",
      "===========================惠州:面积户型================================\n",
      "面积户型\n",
      "中户型     1308\n",
      "小户型      733\n",
      "大户型       95\n",
      "超大户型       8\n",
      "Name: count, dtype: int64\n",
      "===========================江门:室================================\n",
      "室\n",
      "3    1339\n",
      "4     583\n",
      "2     330\n",
      "1     143\n",
      "5     110\n",
      "6      12\n",
      "7       8\n",
      "Name: count, dtype: int64\n",
      "===========================江门:厅================================\n",
      "厅\n",
      "2    1996\n",
      "1     446\n",
      "0      40\n",
      "3      39\n",
      "4       3\n",
      "5       1\n",
      "Name: count, dtype: int64\n",
      "===========================江门:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    2045\n",
      "南北通透     408\n",
      "双向组合      62\n",
      "东西通透       9\n",
      "复杂组合       1\n",
      "Name: count, dtype: int64\n",
      "===========================江门:装修情况================================\n",
      "装修情况\n",
      "其他    1135\n",
      "精装     782\n",
      "毛坯     410\n",
      "简装     198\n",
      "Name: count, dtype: int64\n",
      "===========================江门:楼层等级================================\n",
      "楼层等级\n",
      "中楼层    929\n",
      "高楼层    867\n",
      "低楼层    729\n",
      "Name: count, dtype: int64\n",
      "===========================江门:关注等级================================\n",
      "关注等级\n",
      "低关注    2504\n",
      "中关注      18\n",
      "高关注       3\n",
      "Name: count, dtype: int64\n",
      "===========================江门:发布区间================================\n",
      "发布区间\n",
      "半年以上发布    1569\n",
      "近六个月发布     369\n",
      "近一个月发布     326\n",
      "近三个月发布     261\n",
      "Name: count, dtype: int64\n",
      "===========================江门:总价区间================================\n",
      "总价区间\n",
      "50-100万     1142\n",
      "50万以内        670\n",
      "100-200万     571\n",
      "200万以上       142\n",
      "Name: count, dtype: int64\n",
      "===========================江门:单价区间================================\n",
      "单价区间\n",
      "1万以内    2047\n",
      "1-3万     475\n",
      "3-5万       3\n",
      "Name: count, dtype: int64\n",
      "===========================江门:面积户型================================\n",
      "面积户型\n",
      "中户型     1485\n",
      "小户型      738\n",
      "大户型      179\n",
      "超大户型     123\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:室================================\n",
      "室\n",
      "3    832\n",
      "2    549\n",
      "4    332\n",
      "1    281\n",
      "5     68\n",
      "6      9\n",
      "7      3\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:厅================================\n",
      "厅\n",
      "2    1209\n",
      "1     776\n",
      "0      82\n",
      "3       7\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    1826\n",
      "双向组合     124\n",
      "南北通透     114\n",
      "东西通透       6\n",
      "复杂组合       4\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:装修情况================================\n",
      "装修情况\n",
      "精装    1148\n",
      "简装     510\n",
      "其他     355\n",
      "毛坯      61\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:楼层等级================================\n",
      "楼层等级\n",
      "高楼层    879\n",
      "中楼层    685\n",
      "低楼层    508\n",
      "未分类      2\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:关注等级================================\n",
      "关注等级\n",
      "低关注    1804\n",
      "中关注     256\n",
      "高关注      14\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:发布区间================================\n",
      "发布区间\n",
      "半年以上发布    649\n",
      "近一个月发布    594\n",
      "近六个月发布    431\n",
      "近三个月发布    400\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:总价区间================================\n",
      "总价区间\n",
      "200万以上      1681\n",
      "100-200万     310\n",
      "50-100万       73\n",
      "50万以内         10\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:单价区间================================\n",
      "单价区间\n",
      "3-5万    852\n",
      "5万以上    849\n",
      "1-3万    372\n",
      "1万以内      1\n",
      "Name: count, dtype: int64\n",
      "===========================深圳:面积户型================================\n",
      "面积户型\n",
      "小户型     1448\n",
      "中户型      518\n",
      "大户型       84\n",
      "超大户型      24\n",
      "Name: count, dtype: int64\n",
      "===========================清远:室================================\n",
      "室\n",
      "3     1361\n",
      "4      508\n",
      "2      421\n",
      "1      146\n",
      "5      117\n",
      "6       33\n",
      "7        7\n",
      "9        1\n",
      "8        1\n",
      "11       1\n",
      "Name: count, dtype: int64\n",
      "===========================清远:厅================================\n",
      "厅\n",
      "2    1904\n",
      "1     609\n",
      "3      52\n",
      "0      27\n",
      "4       3\n",
      "5       1\n",
      "Name: count, dtype: int64\n",
      "===========================清远:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    2139\n",
      "南北通透     321\n",
      "双向组合     114\n",
      "东西通透      13\n",
      "复杂组合       9\n",
      "Name: count, dtype: int64\n",
      "===========================清远:装修情况================================\n",
      "装修情况\n",
      "精装    1143\n",
      "其他     743\n",
      "毛坯     463\n",
      "简装     247\n",
      "Name: count, dtype: int64\n",
      "===========================清远:楼层等级================================\n",
      "楼层等级\n",
      "低楼层    878\n",
      "中楼层    876\n",
      "高楼层    842\n",
      "Name: count, dtype: int64\n",
      "===========================清远:关注等级================================\n",
      "关注等级\n",
      "低关注    2552\n",
      "中关注      39\n",
      "高关注       5\n",
      "Name: count, dtype: int64\n",
      "===========================清远:发布区间================================\n",
      "发布区间\n",
      "半年以上发布    1628\n",
      "近六个月发布     355\n",
      "近一个月发布     332\n",
      "近三个月发布     281\n",
      "Name: count, dtype: int64\n",
      "===========================清远:总价区间================================\n",
      "总价区间\n",
      "50-100万     1243\n",
      "50万以内        857\n",
      "100-200万     323\n",
      "200万以上       173\n",
      "Name: count, dtype: int64\n",
      "===========================清远:单价区间================================\n",
      "单价区间\n",
      "1万以内    2386\n",
      "1-3万     210\n",
      "Name: count, dtype: int64\n",
      "===========================清远:面积户型================================\n",
      "面积户型\n",
      "中户型     1385\n",
      "小户型      827\n",
      "超大户型     202\n",
      "大户型      182\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:室================================\n",
      "室\n",
      "3    1242\n",
      "4     592\n",
      "2     415\n",
      "1     154\n",
      "5      95\n",
      "6      21\n",
      "7       2\n",
      "9       1\n",
      "8       1\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:厅================================\n",
      "厅\n",
      "2    2073\n",
      "1     396\n",
      "0      50\n",
      "3       3\n",
      "4       1\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    2237\n",
      "南北通透     188\n",
      "双向组合      90\n",
      "东西通透       5\n",
      "复杂组合       3\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:装修情况================================\n",
      "装修情况\n",
      "精装    1692\n",
      "简装     488\n",
      "毛坯     333\n",
      "其他      10\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:楼层等级================================\n",
      "楼层等级\n",
      "高楼层    1002\n",
      "中楼层     889\n",
      "低楼层     632\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:关注等级================================\n",
      "关注等级\n",
      "低关注    2490\n",
      "中关注      30\n",
      "高关注       3\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:发布区间================================\n",
      "发布区间\n",
      "半年以上发布    1659\n",
      "近六个月发布     346\n",
      "近一个月发布     286\n",
      "近三个月发布     232\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:总价区间================================\n",
      "总价区间\n",
      "50-100万     1102\n",
      "100-200万     843\n",
      "50万以内        501\n",
      "200万以上        77\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:单价区间================================\n",
      "单价区间\n",
      "1万以内    1739\n",
      "1-3万     783\n",
      "3-5万       1\n",
      "Name: count, dtype: int64\n",
      "===========================湛江:面积户型================================\n",
      "面积户型\n",
      "中户型     1476\n",
      "小户型      756\n",
      "大户型      243\n",
      "超大户型      48\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:室================================\n",
      "室\n",
      "3    2149\n",
      "4      52\n",
      "2      30\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:厅================================\n",
      "厅\n",
      "2    1975\n",
      "1     256\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:朝向分类================================\n",
      "朝向分类\n",
      "单一朝向    1817\n",
      "南北通透     328\n",
      "双向组合      85\n",
      "复杂组合       1\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:装修情况================================\n",
      "装修情况\n",
      "其他    889\n",
      "精装    843\n",
      "毛坯    318\n",
      "简装    181\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:楼层等级================================\n",
      "楼层等级\n",
      "中楼层    802\n",
      "高楼层    786\n",
      "低楼层    643\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:关注等级================================\n",
      "关注等级\n",
      "低关注    2196\n",
      "中关注      34\n",
      "高关注       1\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:发布区间================================\n",
      "发布区间\n",
      "半年以上发布    1490\n",
      "近一个月发布     289\n",
      "近六个月发布     258\n",
      "近三个月发布     193\n",
      "未分类          1\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:总价区间================================\n",
      "总价区间\n",
      "50-100万     1554\n",
      "100-200万     664\n",
      "200万以上         8\n",
      "50万以内          5\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:单价区间================================\n",
      "单价区间\n",
      "1万以内    1425\n",
      "1-3万     806\n",
      "Name: count, dtype: int64\n",
      "===========================珠海:面积户型================================\n",
      "面积户型\n",
      "中户型    1923\n",
      "小户型     305\n",
      "大户型       3\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "columns_to_analyze = ['室','厅', '朝向分类', '装修情况', '楼层等级', '关注等级', '发布区间', '总价区间', '单价区间','面积户型']\n",
    "for city in cities:\n",
    "    df = datazs[city]\n",
    "    for i in columns_to_analyze:\n",
    "        print(f'==========================={city}:{i}================================')\n",
    "        print(df[i].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94486701",
   "metadata": {},
   "source": [
    "'标题', '小区名称', '区域', '户型', '面积', '朝向', '装修情况', '楼层', '关注人数', '发布时间',\n",
    "       '总价', '单价', '城市', '新编号', '室', '厅', '楼层等级', '关注等级', '发布区间', '总价区间',\n",
    "       '单价区间', '朝向分类', '面积户型'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37279026",
   "metadata": {},
   "source": [
    "# 分类数据保存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "10187880",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "===========================东莞================================\n",
      "东莞导入成功\n",
      "===========================中山================================\n",
      "中山导入成功\n",
      "===========================佛山================================\n",
      "佛山导入成功\n",
      "===========================广州================================\n",
      "广州导入成功\n",
      "===========================惠州================================\n",
      "惠州导入成功\n",
      "===========================江门================================\n",
      "江门导入成功\n",
      "===========================深圳================================\n",
      "深圳导入成功\n",
      "===========================清远================================\n",
      "清远导入成功\n",
      "===========================湛江================================\n",
      "湛江导入成功\n",
      "===========================珠海================================\n",
      "珠海导入成功\n"
     ]
    }
   ],
   "source": [
    "columns_to_analyze = ['区域','户型','室','厅','面积户型','朝向分类','装修情况', '楼层等级', '关注等级', '发布区间', '总价区间', '单价区间']\n",
    "dir_name = '在售分类'\n",
    "if not os.path.exists(dir_name):\n",
    "        os.makedirs(dir_name)\n",
    "for city, df in datazs.items():\n",
    "    print(f'==========================={city}================================')\n",
    "    city_stats = {}\n",
    "    city_stats['城市']=city\n",
    "    #平均单价、总价\n",
    "    df_clean = df.copy()\n",
    "    df_clean['总价'] = pd.to_numeric(df_clean['总价'], errors='coerce')\n",
    "    df_clean['单价'] = pd.to_numeric(df_clean['单价'], errors='coerce')\n",
    "    # 计算平均值（保留2位小数）\n",
    "    avg_total_price = round(df_clean['总价'].mean(), 2)\n",
    "    avg_unit_price = round(df_clean['单价'].mean(), 2)\n",
    "    # 存入统计字典\n",
    "    city_stats['平均总价（万）'] = avg_total_price\n",
    "    city_stats['平均单价（元/㎡）'] = avg_unit_price\n",
    "    \n",
    "    # 获取TOP10小区在售数量\n",
    "    community_stats = df.groupby('小区名称').agg(\n",
    "        在售数量=('新编号', 'count'),\n",
    "        平均总价=('总价', 'mean'),\n",
    "        平均单价=('单价', 'mean')\n",
    "    ).reset_index()\n",
    "    \n",
    "    # 保留两位小数\n",
    "    community_stats['平均总价'] = community_stats['平均总价'].round(2)\n",
    "    community_stats['平均单价'] = community_stats['平均单价'].round(2)\n",
    "    \n",
    "    # 排序并取前10\n",
    "    top10_communities = community_stats.sort_values('在售数量', ascending=False).head(10)\n",
    "    \n",
    "    # 转换为字典列表\n",
    "    top10_records = top10_communities.rename(columns={\n",
    "        '平均总价': '平均总价（万）',\n",
    "        '平均单价': '平均单价（元/㎡）'\n",
    "    }).to_dict('records')\n",
    "    \n",
    "    city_stats['top10小区'] = top10_records\n",
    "    #其他列分类统计\n",
    "    for col in columns_to_analyze:\n",
    "        counts = df[col].value_counts().to_dict()\n",
    "        city_stats[col] = counts\n",
    "    print(f'{city}导入成功')\n",
    "    with open(f\"{dir_name}/{city}_在售分类统计结果.json\", \"w\", encoding=\"utf-8\") as f:\n",
    "        json.dump(city_stats, f, ensure_ascii=False, indent=2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3b09b50",
   "metadata": {},
   "source": [
    "# 合并数据并保存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "01eeae9b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "已合并所有城市数据，总行数: 22962\n",
      "包含城市: ['东莞', '中山', '佛山', '广州', '惠州', '江门', '深圳', '清远', '湛江', '珠海']\n",
      "\n",
      " 已将合并后的数据保存至: 在售集合/广东在售二手房数据.csv\n"
     ]
    }
   ],
   "source": [
    "# 合并所有城市的数据\n",
    "all_data = pd.concat(\n",
    "    [datazs[city] for city in cities],\n",
    "    ignore_index=True  # 重置索引\n",
    ")\n",
    "\n",
    "# 查看合并后的数据概况\n",
    "print(f\"已合并所有城市数据，总行数: {len(all_data)}\")\n",
    "print(f\"包含城市: {all_data['城市'].unique().tolist()}\")\n",
    "\n",
    "# 按照指定顺序重新排列列\n",
    "visualization_cols = [\n",
    "    '新编号', '区域', '城市', \n",
    "    '标题', '小区名称', \n",
    "    '户型', '室', '厅',\n",
    "    '面积', '面积户型', \n",
    "    '朝向', '朝向分类', \n",
    "    '装修情况',\n",
    "    '楼层', '楼层等级',\n",
    "    '关注人数', '关注等级', \n",
    "    '发布时间', '发布区间', \n",
    "    '总价', '总价区间', '单价', '单价区间'\n",
    "]\n",
    "\n",
    "all_data = all_data.reindex(columns=visualization_cols)\n",
    "# 保存为CSV文件\n",
    "if not os.path.exists('在售集合'):\n",
    "        os.makedirs('在售集合')\n",
    "output_path = f\"{'在售集合'}/广东在售二手房数据.csv\"\n",
    "all_data.to_csv(\n",
    "    output_path,\n",
    "    index=False,           # 不保存行索引\n",
    "    na_rep='nan',          # 缺失值用nan表示\n",
    "    encoding='utf-8-sig'   \n",
    ")\n",
    "\n",
    "print(f\"\\n 已将合并后的数据保存至: {output_path}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "47d9831f",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
